//
//  DataBaseHandler.m
//  解闷
//
//  Created by dllo on 15/7/17.
//  Copyright (c) 2015年 lanou. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler

+ (DataBaseHandler *)shareInstance
{
    // static  一个应用程序执行期间,只会执行一次
    static DataBaseHandler *dbHandler = nil;
    
    if (dbHandler == nil) {
        // 如果指针指向空 , 就创建一个对象
        dbHandler = [[DataBaseHandler alloc]init];
        [dbHandler openDB];
    }
    return dbHandler;
}


- (void)openDB
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES)lastObject];
    
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"dataBase.db"];
    
    // 参数1:数据库文件的路径
    // 参数2:数据库指针的指针
    
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    
    NSFileManager *manager = [NSFileManager defaultManager];
    if (![manager fileExistsAtPath:dbPath]) {
        NSString *source = [[NSBundle mainBundle]pathForResource:@"dataBase.db" ofType:nil];
        [manager copyItemAtPath:source toPath:dbPath error:nil];
    }
    
    NSLog(@"执行结果: %d",result);
    NSLog(@"路径 : %@",dbPath);
    
}

- (void)closeDB
{
    sqlite3_close(dbPoint);
}


- (void)createTableWithString:(NSString *)str
{
    NSString *sql = [NSString stringWithFormat:@"create table %@ (serialName,Picture,serialId primary key)",str];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    NSLog(@"创建表结果: %d",result);

  
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
     
    
    }else{
        NSLog(@"创建表失败");
    }
    
}

- (void)insertmodel:(modelNew *)model str:(NSString *)str
{
//    NSString *astr = [NSString stringWithFormat:@"%@", model.id];
    NSString *sql = [NSString stringWithFormat:@"insert into %@ values('%@','%@','%@')",str,model.serialName,model.Picture,model.serialId];
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    // 判断是否执行成功
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
      

        
        
    }else{
        NSLog(@"插入失败");
    }

}

- (NSArray *)selectAllWithstr:(NSString *)str
{
    //1.创建一个数据库指针的替身
    //替身作为一个临时的数据库指针, 保存所有对数据库的操作, 最终确认无误后写入本地数据库
    
    sqlite3_stmt *stmt = nil;
    
    //2.检查sql语句, 准备执行
    //作用:把替身和数据库指针连接起来
    
    //参数1:数据库指针
    //参数2:sql语句
    NSString *sql = [NSString stringWithFormat:@"select * from %@",str];
    //参数3:限制sql语句的长度(-1为不限制)
    //参数4:替身的指针
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    
    //对sql语句的检查结果进行判断
    if (result == SQLITE_OK) {
        
        NSMutableArray *array = [NSMutableArray array];
        
        //当符合sql语句查询条件的结果有多行, 就执行循环体的代码
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //参数1:替身
            //参数2:取的是第几列
            
            const unsigned char *serialName = sqlite3_column_text(stmt, 0);
            const unsigned char *Picture = sqlite3_column_text(stmt, 1);
            const unsigned char *serialId = sqlite3_column_text(stmt, 2);
            
            
            
            NSString *serialName1 = [NSString stringWithUTF8String:(const char * )serialName];
            NSString *Picture1 = [NSString stringWithUTF8String:(const char *)Picture];
            NSString *serialId1 = [NSString stringWithUTF8String:(const char *)serialId];
            //把拿到的nsstring数据给一个学生对象赋值
            
//            modelNew *model = [modelNew modelWithtext:text1 name:name1 profile_image:profile_image1 passtime:passtime1 bimageuri:bimageuri1 videouri:videouri1 videotime:videotime1 voiceuri:voiceuri1 voicetime:voicetime1 playcount:playcount1 image0:image01 id:id1 width:width height:height];
            modelNew *model = [[modelNew alloc]init];
            model.serialName = serialName1;
            model.Picture = Picture1;
            model.serialId = serialId1;

            
            //把创建好的学生对象添加到数组中
            [array addObject:model];
        }
        //释放替身的内存占用, 将替身的所有操作应用到底层数据库文件
        sqlite3_finalize(stmt);
        return array;
    }
    //若失败,返回空
    sqlite3_finalize(stmt);
    return nil;
}

//  找数据库 已经存在的表
- (NSMutableArray *)selectTableName
{
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    //拼接一个数据库文件路径
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"dataBase.db"];
    
    sqlite3 *database;
    sqlite3_open([dbPath UTF8String], &database);
    
    sqlite3_stmt *statement;
    const char *getTableInfo = "select * from sqlite_master where type='table' order by name";
    sqlite3_prepare_v2(database, getTableInfo, -1, &statement, nil);
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        char *nameData = (char *)sqlite3_column_text(statement, 1);
        NSString *tableName = [[NSString alloc] initWithUTF8String:nameData];
        NSLog(@"name:%@",tableName);
        
        [arr addObject:tableName];
        
    }
    return arr;

}

- (void)truncateWithtable:(NSString *)strr
{
    NSString *sql = [NSString stringWithFormat:@"Truncate table '%@'",strr];
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}


- (void)delegateWithtable:(NSString *)strr
{
    NSString *sql = [NSString stringWithFormat:@"(drop table '%@')",strr];
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}




- (void)deleteWithModel:(modelNew *)model tablename:(NSString *)strr
{
    NSString *sql = [NSString stringWithFormat:@"delete from '%@' where serialId = '%@'",strr,model.serialId];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}


@end
